﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;
using System.Data.OleDb;
using System.Data;

namespace ZLCMS.Web.Code
{
    public class ExcelLoader
    {
        public string ConnectionString { get; set; }

        public IList<IDictionary> LoadSource(string name)
        {
            using (OleDbConnection conn = new OleDbConnection(ConnectionString))
            {
                conn.Open();
                DataTable tables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                IDbCommand command = conn.CreateCommand();
                command.CommandText = " select * from   [" + tables.Rows[0]["TABLE_NAME"] + "]";
                IList<IDictionary> list = new List<IDictionary>();
                IDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    IDictionary tuple = new Dictionary<string, object>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        tuple[reader.GetName(i)] = reader[i];
                    }
                    list.Add(tuple);
                }

                reader.Close();
                conn.Close();

                return list;
            }

        }

        public IList<string> LoadColumns(string name)
        {
            using (OleDbConnection conn = new OleDbConnection(ConnectionString))
            {
                conn.Open();
                IList<string> list = new List<string>();
                DataTable tables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                string tb = tables.Rows[0]["TABLE_NAME"] as string;
                DataTable columns = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tables.Rows[0]["TABLE_NAME"], null });
                foreach (DataRow row in columns.Rows)
                {
                    //                     for (int i = 0; i < columns.Columns.Count; i++ )
                    //                     {
                    //                         list.Add(columns.Columns[i].ColumnName);
                    //                     }
                    list.Add(row["COLUMN_NAME"].ToString());
                }
                return list;
            }
        }
    }
}
